import pandas as pd
import datetime
import pymysql
import time
import to_sql #写入数据库函数

#连接本地数据库
mydb = pymysql.connect(
    host='localhost',
    port=3306,#mysql默认端口号
    user='',#数据库安全该部分不展示
    password='',
    charset='utf8',
    autocommit=True # 自动二次确认
)
mycursor = mydb.cursor()


#强制设置为utf8mb4,适配🔥等4字节的utf8编码
mycursor.execute('SET NAMES utf8mb4')
mycursor.execute("SET CHARACTER SET utf8mb4")
mycursor.execute("SET character_set_connection=utf8mb4")


#进入news数据库
mycursor.execute("USE news;")

#下面将data的对应内容注入数据库中的主榜单news_main中
def mainlist(data):
    tablename='news_main'
    #首先统计数据库中的list批次，用于更新要插入的list_order
    #注入数据
    for i in range(0,len(data)):
        title="'"+data.loc[i,'content']+"'"#标题
        rank=str(i+1)#排名
        description="'"+data.loc[i,'description']+"'"#新闻简介
        imagesrc="'"+data.loc[i,'imagesrc']+"'"#图片url
        href="'"+data.loc[i,'url']+"'"#url
        source="'"+data.loc[i,'source']+"'"#source
        d = datetime.datetime.today()
        datetime_sql="'"+str(d.year)+'-'+str(d.month).zfill(2)+'-'+str(d.day).zfill(2)+' '+str(d.hour)+':'+str(d.minute)+':'+str(d.second)+"'"
        #在新表中注入Dataframe的内容，这里采用逐元组添加的方式,id自增长，value设置为null
        s="INSERT INTO "+tablename+" value"+ '(' + 'null,' + rank +','+ title+',' + description +','+ imagesrc +','+ href +','+ source +','+  datetime_sql +');'
        mycursor.execute(s)

#优化sql写入速度问题：将整个list的insert语句和合并为insert into table_name +values(),()...
def Dataframe2sql_child(data,tablename):
    #首先统计tablename中已有的list数目——num_order
    # s_order='select count(*) from '+ tablename +' where `rank`=1;'
    # mycursor.execute(s_order)
    # for x in mycursor:
    #     num_order=str(x[0])
    s="INSERT INTO "+tablename+" values"
    #注入数据
    for i in range(0,len(data)):
        title="'"+data.loc[i,'content']+"'"#标题
        rank=str(i+1)#排名
        href="'"+data.loc[i,'url']+"'"#url
        source="'"+data.loc[i,'source']+"'"#source
        # print(data.loc[i,'image_icon'],type(data.loc[i,'image_icon']))
        image_icon="'"+data.loc[i,'image_icon']+"'"#image_icon
        d = datetime.datetime.today()#datetime
        datetime_sql="'"+str(d.year)+'-'+str(d.month).zfill(2)+'-'+str(d.day).zfill(2)+' '+str(d.hour)+':'+str(d.minute)+':'+str(d.second)+"'"

        #在新表中注入Dataframe的内容，这里采用逐元组添加的方式,id自增长，value设置为null
        #这里进行一个异常判定，将title过长的字符截取，只取前100位
        if(len(title)>200):
            title=title[0:100]+"'"
        if(len(href)>955):
            href="'https://www.zhihu.com/404'"#过长的网址直接404
        s=s+'(' + 'null,' + rank +','+ title+',' + href +','+ source +','+ image_icon +','+ datetime_sql +'),'
    #删除最后一个','并添加';'
    s=s[0:-1]+';'
    #print(s)
    mycursor.execute(s)


#写入小榜单的函数to_sql.sublist(category,datalist)包含参数:category(首页0、综合1、科技2、娱乐3、社区4、财经5、开发6、报刊7) datalist[](写入对应数据库news_child_category的内容)
def sublist(category,datalist):
    for i in range(0,len(datalist)):
        # print("list ",i," to_sql")
        #首先create对应的news_child_category_i(if not exists like template_table) d
        s='create table '+'if not exists '+'news_child_'+str(category) +'_'+ str(i)+' like template_table'+';'
        mycursor.execute(s)

        #通过Dataframe2sql_child函数将datalist[i]的内容注入到表news_child_category_i中
        tablename='news_child_'+str(category) +'_'+ str(i)
        # print("insert list i:")
        Dataframe2sql_child(datalist[i],tablename)




